from datetime import datetime
from typing import List, Tuple, Dict
from fastapi import HTTPException, UploadFile
from tortoise.contrib.pydantic import pydantic_model_creator, PydanticModel
from model.sysRoleModel import SysRole
from schema.sysRoleSchema import SysRoleCreate, SysRoleUpdate, PageParams, SysRoleResponse, RoleChange, RoleData
from io import BytesIO
from excel.sysRoleExcel import SysRoleExcel
from tortoise import connections, transactions
from service.sysRoleDeptService import SysRoleDeptService
from service.sysMenuService import SysMenuService
from service.sysRoleMenuService import SysRoleMenuService


class PydanticConfig:
    alias_generator = lambda x: ''.join(word.capitalize() if i else word for i, word in enumerate(x.split('_')))
    allow_population_by_field_name = True


SysRole_Pydantic = pydantic_model_creator(SysRole, name="SysRole", config_class=PydanticConfig)

role_dept_service = SysRoleDeptService()
menu_service = SysMenuService()
role_menu_service = SysRoleMenuService()


class SysRoleService:
    @staticmethod
    async def create(data: SysRoleCreate, creator: str) -> PydanticModel:
        """Create new record"""
        data_dict = data.dict(exclude_unset=True)
        data_dict["create_by"] = creator
        data_dict["create_time"] = datetime.now()
        data_dict["status"] = '0'

        obj = await SysRole.create(**data_dict)
        return await SysRole_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def get_by_id(id: int) -> PydanticModel:
        """Get record by ID"""
        obj = await SysRole.get_or_none(role_id=id, status='0')
        if not obj:
            raise HTTPException(status_code=404, detail="角色信息表不存在")

        menus_ids = await menu_service.find_id_list_by_role(id)
        role_data = await SysRole_Pydantic.from_tortoise_orm(obj)

        role_response = SysRoleResponse(**role_data.dict())
        result = role_response.dict(by_alias=True)
        result['menus'] = menus_ids

        return result

    @staticmethod
    async def update(id: int, data: SysRoleUpdate, updater: str, only=False) -> PydanticModel:
        """Update record"""
        async with transactions.in_transaction() as transaction:
            obj = await SysRole.get_or_none(role_id=id, status='0')
            if not obj:
                raise HTTPException(status_code=404, detail="角色信息表不存在")

            update_dict = data.dict(exclude_unset=True)
            update_dict["update_by"] = updater
            update_dict["update_time"] = datetime.now()

            await obj.update_from_dict(update_dict).save()
            if not only:
                await role_menu_service.batch_create_by_dept_list(id, data.menu_ids, updater)
            return await SysRole_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def delete(ids: List[int], updater: str) -> int:
        """Batch delete records"""
        update_data = {
            "status": '2',
            "update_by": updater,
            "update_time": datetime.now()
        }
        count = await SysRole.filter(role_id__in=ids, status='0').update(**update_data)
        return count

    @staticmethod
    async def get_page(params: PageParams) -> tuple[list[PydanticModel], int]:
        """Get page list"""
        query = SysRole.filter(status='0')

        # Build query conditions

        if params.role_name:
            query = query.filter(role_name__icontains=params.role_name)

        if params.role_key:
            query = query.filter(role_key__icontains=params.role_key)

        if params.data_scope:
            query = query.filter(data_scope__icontains=params.data_scope)

        if params.status:
            query = query.filter(status__icontains=params.status)

        if params.use_flag:
            query = query.filter(use_flag__icontains=params.use_flag)

        if params.create_by:
            query = query.filter(create_by__icontains=params.create_by)

        if params.update_by:
            query = query.filter(update_by__icontains=params.update_by)

        if params.remark:
            query = query.filter(remark__icontains=params.remark)

        total = await query.count()

        records = await query.offset((params.page_num - 1) * params.page_size).limit(params.page_size).order_by(
            "role_sort")

        return [await SysRole_Pydantic.from_tortoise_orm(record) for record in records], total

    @staticmethod
    async def export_excel() -> BytesIO:
        """Export all data to Excel"""
        records = await SysRole.filter(status='0').all()
        return await SysRoleExcel.export_data(records)

    @staticmethod
    def get_import_template() -> BytesIO:
        """Get import template"""
        return SysRoleExcel.get_import_template()

    @staticmethod
    async def import_data(file: UploadFile, creator: str) -> Tuple[int, List[str]]:
        """Import data"""
        content = await file.read()
        file_bytes = BytesIO(content)

        try:
            data_list = SysRoleExcel.parse_import_data(file_bytes)
        except ValueError as e:
            raise HTTPException(status_code=400, detail=str(e))
        except Exception as e:
            raise HTTPException(status_code=400, detail="Excel文件格式错误")

        success_count = 0
        error_msgs = []

        for index, item_data in enumerate(data_list, start=1):
            try:
                item_data['create_by'] = creator
                item_data['create_time'] = datetime.now()
                item_data['status'] = '0'

                await SysRole.create(**item_data)
                success_count += 1

            except Exception as e:
                error_msgs.append(f"第{index}行: {str(e)}")

        return success_count, error_msgs

    @staticmethod
    async def get_role_keys_by_user(user_id: int):
        """获取用户的角色 key 列表"""
        query = """
                SELECT DISTINCT r.*
                FROM sys_role r
                INNER JOIN sys_user_role ur ON ur.role_id = r.role_id
                WHERE ur.user_id = %s
                AND r.status = '0'  
                AND r.use_flag = '0'
                ORDER BY r.role_sort ASC
            """

        conn = connections.get('default')
        results = await conn.execute_query_dict(query, [user_id])

        return [result['role_key'] for result in results]

    pass

    @staticmethod
    async def get_role_all_list() -> List[Dict]:
        """获取用户的角色列表
                Returns:
                    List[Dict]: 角色信息列表，包含角色的完整信息（驼峰式命名）
                """
        query = """
                    SELECT DISTINCT r.role_id, r.role_name, r.role_key, r.role_sort, 
                           r.data_scope, r.use_flag, r.create_time, r.create_by,
                           r.update_time, r.update_by, r.remark, 
                           r.menu_check_strictly, r.dept_check_strictly, r.status
                    FROM sys_role r
                    WHERE r.status = '0'  
                    AND r.use_flag = '0'
                    ORDER BY r.role_sort ASC
                """

        conn = connections.get('default')
        results = await conn.execute_query_dict(query)

        formatted_results = []
        for result in results:
            if result.get('create_time'):
                result['create_time'] = result['create_time'].strftime('%Y-%m-%d %H:%M:%S')
            if result.get('update_time'):
                result['update_time'] = result['update_time'].strftime('%Y-%m-%d %H:%M:%S')

            # 使用 schema 进行转换，确保字段名为驼峰式
            role_model = SysRoleResponse(**result)
            formatted_results.append(role_model.dict(by_alias=True))

        return formatted_results

    async def get_role_list_by_user(self, user_id: int) -> List[Dict]:
        """获取用户的角色列表

        Args:
            user_id (int): 用户ID

        Returns:
            List[Dict]: 角色信息列表，包含角色的完整信息（驼峰式命名）
        """

        if user_id == 1:
            return await self.get_role_all_list()

        query = """
                    SELECT DISTINCT r.role_id, r.role_name, r.role_key, r.role_sort, 
                           r.data_scope, r.use_flag, r.create_time, r.create_by,
                           r.update_time, r.update_by, r.remark, 
                           r.menu_check_strictly, r.dept_check_strictly, r.status
                    FROM sys_role r
                    INNER JOIN sys_user_role ur ON ur.role_id = r.role_id
                    WHERE ur.user_id = %s
                    AND r.status = '0'  
                    AND r.use_flag = '0'
                    ORDER BY r.role_sort ASC
                """

        conn = connections.get('default')
        results = await conn.execute_query_dict(query, [user_id])

        # 处理日期时间格式并使用schema转换
        formatted_results = []
        for result in results:
            if result.get('create_time'):
                result['create_time'] = result['create_time'].strftime('%Y-%m-%d %H:%M:%S')
            if result.get('update_time'):
                result['update_time'] = result['update_time'].strftime('%Y-%m-%d %H:%M:%S')

            # 使用 schema 进行转换，确保字段名为驼峰式
            role_model = SysRoleResponse(**result)
            formatted_results.append(role_model.dict(by_alias=True))

        return formatted_results

    @staticmethod
    async def change_status(role: RoleChange, updater: str):
        # 获取用户基本信息
        obj = await SysRole.get_or_none(role_id=role.role_id, status='0')
        if not obj:
            raise HTTPException(status_code=201, detail="角色不存在")

        update_dict = role.dict(exclude_unset=True)
        update_dict["update_by"] = updater
        update_dict["update_time"] = datetime.now()

        await obj.update_from_dict(update_dict).save()
        return await SysRole_Pydantic.from_tortoise_orm(obj)

    async def save_data_scope(self, data: RoleData, updater: str):
        async with transactions.in_transaction() as transaction:
            update_data = SysRoleUpdate(
                role_name=data.role_name,
                role_key=data.role_key,
                role_sort=data.role_sort,
                data_scope=data.data_scope,
                menu_check_strictly=data.menu_check_strictly,
                dept_check_strictly=data.dept_check_strictly,
                status=data.status,
                use_flag=data.use_flag,
                remark=data.remark,
                menu_ids=[]
            )

            await self.update(data.role_id, update_data, updater, only=True)
            await role_dept_service.batch_create_by_dept_list(data.role_id, data.dept_ids, updater)

        return data
